--Step 8.5 Write five DML commands for the new database, illustrating differences from the strictly relational DML.

--1. Find the names of all artists who were interviewed after January 1, 2009 but who have no works of art listed. 
SELECT a.name.firstName, a.name.lastName
FROM Artists a
WHERE a.interviewDate > '01-Jan-2009' AND a.pid NOT IN (SELECT a.aid.pid
							FROM Artworks a);


--2. Find the total commission for salesperson John Smith earned between the dates April 1, 2011 and April 15, 2011. --Recall that the gallery charges 10% commission, and the salesperson receives one-half of that, which is 5% of the --selling price.
SELECT .05 * SUM(s.salePrice) AS Commission_for_John_Smith
FROM Sales s
WHERE s.saleDate >='01-Apr-2011' AND s.saleDate <='15-Apr-2011' AND
	s.sid.pid = (SELECT p.pid
		FROM Salespeople p
		WHERE p.name.firstName= 'John' AND p.name.lastName ='Smith');

--3.Find the collector names and artwork titles of all artworks that are owned by collectors, not by the --artists themselves, in order by the collectors last name.

SELECT  c.name.lastName as Coll, w.workTitle as Title
FROM Collectors c, Artworks w 
WHERE w.ownId.pid=c.pid and w.ownId.pid IN (SELECT pid FROM Collectors)
ORDER BY c.name.lastName;

--4.For each Customer, find information about shows that feature his or her preferred artist. 

SELECT c.name.lastName As Customer, s.showOpeningDate As Opens, s.showClosingDate AS Closes,s.showTitle as Show
FROM Shows s , Customers c
WHERE s.aid.pid = c.aid.pid
ORDER BY c.name.lastName;

--5.Find the average sale price of works of artist Winslow Homes.
SELECT AVG(s.salePrice)  AS AveragePrice_for_Homes
FROM Sales s
WHERE s.workId.artworkID IN (SELECT w.artworkid
		FROM Artworks w
		WHERE w.aid.pid = (SELECT a.pid
				FROM Artists a
				WHERE a.name.lastName = 'Homes' AND a.name.firstName ='Winslow'));

